//
//  SPSQLExporter.m
//  sequel-pro
//
//  Created by Stuart Connolly (stuconnolly.com) on August 29, 2009.
//  Copyright (c) 2009 Stuart Connolly. All rights reserved.
//
//  Permission is hereby granted, free of charge, to any person
//  obtaining a copy of this software and associated documentation
//  files (the "Software"), to deal in the Software without
//  restriction, including without limitation the rights to use,
//  copy, modify, merge, publish, distribute, sublicense, and/or sell
//  copies of the Software, and to permit persons to whom the
//  Software is furnished to do so, subject to the following
//  conditions:
//
//  The above copyright notice and this permission notice shall be
//  included in all copies or substantial portions of the Software.
//
//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
//  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
//  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
//  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
//  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
//  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
//  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
//  OTHER DEALINGS IN THE SOFTWARE.
//
//  More info at <https://github.com/sequelpro/sequelpro>

#import "SPSQLExporter.h"
#import "SPTablesList.h"
#import "SPFileHandle.h"
#import "SPExportUtilities.h"
#import "SPExportFile.h"
#import "SPTableData.h"
#import "RegexKitLite.h"
#import "SPExportController.h"
#import "SPFunctions.h"

#import <SPMySQL/SPMySQL.h>
#include <stdlib.h>

@interface SPSQLExporter ()

- (NSString *)_createViewPlaceholderSyntaxForView:(NSString *)viewName;

@end

@implementation SPSQLExporter

@synthesize delegate;
@synthesize sqlExportTables;
@synthesize sqlDatabaseHost;
@synthesize sqlDatabaseName;
@synthesize sqlDatabaseVersion;
@synthesize sqlExportCurrentTable;
@synthesize sqlExportErrors;
@synthesize sqlOutputIncludeUTF8BOM;
@synthesize sqlOutputEncodeBLOBasHex;
@synthesize sqlOutputIncludeErrors;
@synthesize sqlOutputIncludeAutoIncrement;
@synthesize sqlOutputIncludeGeneratedColumns;
@synthesize sqlCurrentTableExportIndex;
@synthesize sqlInsertAfterNValue;
@synthesize sqlInsertDivider;

/**
 * Initialise an instance of SPSQLExporter using the supplied delegate.
 *
 * @param exportDelegate The exporter delegate
 *
 * @return The initialised instance
 */
- (instancetype)initWithDelegate:(NSObject<SPSQLExporterProtocol> *)exportDelegate
{
    if ((self = [super init])) {
        SPExportDelegateConformsToProtocol(exportDelegate, @protocol(SPSQLExporterProtocol));

        [self setDelegate:exportDelegate];
        [self setSqlExportCurrentTable:nil];

        [self setSqlInsertDivider:SPSQLInsertEveryNDataBytes];
        [self setSqlInsertAfterNValue:250000];
    }

    return self;
}

- (void)exportOperation
{
    // used in end_cleanup
    NSMutableString *errors     = [[NSMutableString alloc] init];
    NSMutableString *sqlString  = [[NSMutableString alloc] init];
    NSString *oldSqlMode        = nil;

    // Check that we have all the required info before starting the export
    if ((![self sqlExportTables])     || ([[self sqlExportTables] count] == 0)          ||
        (![self sqlDatabaseHost])     || ([[self sqlDatabaseHost] isEqualToString:@""]) ||
        (![self sqlDatabaseName])     || ([[self sqlDatabaseName] isEqualToString:@""]) ||
        (![self sqlDatabaseVersion]   || ([[self sqlDatabaseName] isEqualToString:@""])))
    {
        [self endCleanup:oldSqlMode];
        return;
    }

    sqlTableDataInstance = [[SPTableData alloc] init];
    [sqlTableDataInstance setConnection:connection];

    // Inform the delegate that the export process is about to begin
    [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBegin:) withObject:self waitUntilDone:NO];

    // Mark the process as running
    [self setExportProcessIsRunning:YES];

    // Clear errors
    [self setSqlExportErrors:@""];

    NSMutableArray *tables = [NSMutableArray array];
    NSMutableArray *procs  = [NSMutableArray array];
    NSMutableArray *funcs  = [NSMutableArray array];

    // Copy over the selected item names into tables in preparation for iteration
    for (NSArray *item in [self sqlExportTables])
    {
        // Check for cancellation flag
        if ([self isCancelled]) {
            [self endCleanup:oldSqlMode];
            return;
        }

        NSMutableArray *targetArray;
        switch ([[item safeObjectAtIndex:4] intValue]) {
            case SPTableTypeProc:
                targetArray = procs;
                break;
            case SPTableTypeFunc:
                targetArray = funcs;
                break;
            case SPTableTypeTable:
            default:
                targetArray = tables;
                break;
        }

        [targetArray addObject:item];
    }

    NSMutableString *metaString = [NSMutableString string];

    // If required write the UTF-8 Byte Order Mark (BOM)
    if ([self sqlOutputIncludeUTF8BOM]) {
        [metaString appendString:@"\xef\xbb\xbf"];
    }

    // we require utf8mb4
    [connection setEncoding:@"utf8mb4"];

    // Add the dump header to the dump file
    [metaString appendString:@"# ************************************************************\n"];
    [metaString appendString:@"# Sequel Ace SQL dump\n"];
    [metaString appendFormat:@"# %@ %@\n#\n", NSLocalizedString(@"Version", @"export header version label"), [[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]];
    [metaString appendFormat:@"# %@\n# %@\n#\n", SPLOCALIZEDURL_HOMEPAGE, SPDevURL];
    [metaString appendFormat:@"# %@: %@ (MySQL %@)\n", NSLocalizedString(@"Host", @"export header host label"), [self sqlDatabaseHost], [self sqlDatabaseVersion]];
    [metaString appendFormat:@"# %@: %@\n", NSLocalizedString(@"Database", @"export header database label"), [self sqlDatabaseName]];
    [metaString appendFormat:@"# %@: %@\n", NSLocalizedString(@"Generation Time", @"export header generation time label"), [NSDate date]];
    [metaString appendString:@"# ************************************************************\n\n\n"];

    // Add commands to store the client encodings used when importing and set to UTF8mb4 to preserve data
    [metaString appendString:@"/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n"];
    [metaString appendString:@"/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n"];
    [metaString appendString:@"/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n"];
    [metaString appendString:@"SET NAMES utf8mb4;\n"];

    [metaString appendString:@"/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n"];

    /* A note on SQL_MODE:
     *
     *   BEFORE 3.23.6
     *     No supported
     *
     *   FROM 3.23.6
     *     There is a "--ansi" / "-a" CLI argument to mysqld, which is the predecessor to SQL_MODE.
     *     It can be queried via "SHOW VARIABLES" -> "ansi_mode" = "OFF" | "ON"
     *
     *   FROM 3.23.41
     *     There is a "--sql-mode=[opt[,opt[...]]]" CLI argument to mysqld.
     *     It can be queried via "SHOW VARIABLES" -> "sql_mode" but the result will be a bitfield value with
     *         #define MODE_REAL_AS_FLOAT        1 = "REAL_AS_FLOAT"
     *         #define MODE_PIPES_AS_CONCAT      2 = "PIPES_AS_CONCAT"
     *         #define MODE_ANSI_QUOTES          4 = "ANSI_QUOTES"
     *         #define MODE_IGNORE_SPACE         8 = "IGNORE_SPACE"
     *         #define MODE_SERIALIZABLE        16 = "SERIALIZE" (!)
     *         #define MODE_ONLY_FULL_GROUP_BY  32 = "ONLY_FULL_GROUP_BY"
     *     The "--ansi" switch is still supported but mostly equivalent to setting all of the options above
     *     (it will also set the transaction isolation level to SERIALIZABLE).
     *     "ansi_mode" is no longer returned by SHOW VARIABLES.
     *
     *   FROM 4.1.0
     *     - "sql_mode" can be changed at runtime (global or per session).
     *     - "SHOW VARIABLES" now returns a CSV list of named options
     *
     *   FROM 4.1.1
     *     - "SERIALIZE" is no longer supported (must be changed via "SET TRANSACTION ISOLATION LEVEL")
     *       (trivia: internally it has become MODE_NOT_USED: 16 = "?")
     *
     */

    //fetch old sql mode to restore it later
    {
        SPMySQLResult *result = [connection queryString:@"SHOW VARIABLES LIKE 'sql_mode'"];
        if(![connection queryErrored]) {
            [result setReturnDataAsStrings:YES];
            NSArray *row = [result getRowAsArray];
            oldSqlMode = [[row objectAtIndex:1] unboxNull];
            SPLog(@"oldSqlMode: %@", oldSqlMode);
        }
    }
    //set sql mode for export
    if([@"" isEqualToString:oldSqlMode]) {
        // the current sql_mode is already the one we want (empty string), no need to change+revert it
        oldSqlMode = nil;
    }
    else {
        [connection queryString:@"SET SQL_MODE=''"]; //mysqldump uses a conditional comment for 40100 here, but we want to see the error, since it can't simply be ignored (also ANSI mode is supported before 4.1)
        if ([connection queryErrored]) {
            [errors appendFormat:@"%@ (%@)\n", NSLocalizedString(@"The server's SQL_MODE could not be changed to one suitable for export. The export may be missing important details or may not be importable at all!", @"sql export : 'set @@sql_mode' query failed message"), [connection lastErrorMessage]];
            [metaString appendFormat:@"# SET SQL_MODE Error: %@\n\n\n", [connection lastErrorMessage]];
            //if we couldn't change it, we don't need to restore it either
            oldSqlMode = nil;
        }
    }
    // * There is no point in writing out that the file should use a specific SQL mode when we don't even know which one was active during export.
    // * Triggers, procs/funcs have their own SQL_MODE which is still set/reset below, though.
    //
    // But an unknown SQL_MODE could perhaps still affect how MySQL returns the "SHOW CREATE…"
    // data for those objects (like it does for "SHOW CREATE TABLE") possibly rendering them invalid (need to check that),
    // so it may be better to flat out refuse to export schema object DDL if we don't have a valid sql_mode.


    // JCS Note: per docs: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_auto_value_on_zero
    // For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values,
    // resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem.
    // For this reason, mysqldump automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO.
    //
    // so to address issue #865, where creating a table with a trigger discards NO_AUTO_VALUE_ON_ZERO,
    // by setting SESSION SQL_MODE to the mode used when the trigger was created then resetting SQL_MODE to @OLD_SQL_MODE
    // we add NO_AUTO_VALUE_ON_ZERO to @OLD_SQL_MODE here, for the export file to properly work.
    [metaString appendString:@"/*!40101 SET @OLD_SQL_MODE='NO_AUTO_VALUE_ON_ZERO', SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n"];
    [metaString appendString:@"/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n\n\n"];

    [self writeString:metaString];

    NSMutableDictionary *viewSyntaxes = [NSMutableDictionary dictionary];

    // Loop through the selected tables
    for (NSArray *table in tables) {
        @autoreleasepool {

            if(self.exportOutputFile.fileHandleError != nil){
                SPMainQSync(^{
                    [(SPExportController*)self->delegate cancelExportForFile:self->exportOutputFile.exportFilePath];
                });
                return;
            }

            // Check for cancellation flag
            if ([self isCancelled]) {
                [self endCleanup:oldSqlMode];
                return;
            }

            [self setSqlCurrentTableExportIndex:[self sqlCurrentTableExportIndex]+1];
            NSString *tableName = [table firstObject];

            BOOL sqlOutputIncludeStructure  = [[table safeObjectAtIndex:1] boolValue];
            BOOL sqlOutputIncludeContent    = [[table safeObjectAtIndex:2] boolValue];
            BOOL sqlOutputIncludeDropSyntax = [[table safeObjectAtIndex:3] boolValue];

            // Skip tables if not set to output any detail for them
            if (!sqlOutputIncludeStructure && !sqlOutputIncludeContent && !sqlOutputIncludeDropSyntax) {
                continue;
            }

            // Set the current table
            [self setSqlExportCurrentTable:tableName];

            // Inform the delegate that we are about to start fetcihing data for the current table
            [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginFetchingData:) withObject:self waitUntilDone:NO];

            NSUInteger lastProgressValue = 0;

            id createTableSyntax = nil;
            SPTableType tableType = SPTableTypeTable;
            // Determine whether this table is a table or a view via the CREATE TABLE command, and keep the create table syntax
            {
                SPMySQLResult *queryResult = [connection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", [tableName backtickQuotedString]]];

                [queryResult setReturnDataAsStrings:YES];

                if ([queryResult numberOfRows]) {
                    NSDictionary *tableDetails = [[NSDictionary alloc] initWithDictionary:[queryResult getRowAsDictionary]];

                    if ([tableDetails objectForKey:@"Create View"]) {
                        [viewSyntaxes
                            setValue: [NSString stringWithFormat:@"%@%@",
                                            (sqlOutputIncludeDropSyntax ? [NSString stringWithFormat:@"DROP TABLE IF EXISTS %@; DROP VIEW IF EXISTS %@;\n\n", [tableName backtickQuotedString], [tableName backtickQuotedString]] : @""),
                                            [[[tableDetails objectForKey:@"Create View"] copy] createViewSyntaxPrettifier]]
                            forKey: tableName
                        ];
                        createTableSyntax = [self _createViewPlaceholderSyntaxForView:tableName];
                        tableType = SPTableTypeView;
                    }
                    else {
                        createTableSyntax = [[tableDetails objectForKey:@"Create Table"] copy];
                        tableType = SPTableTypeTable;
                    }
                }

                if ([connection queryErrored]) {
                    [errors appendFormat:@"%@\n", [connection lastErrorMessage]];

                    [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n\n\n", [connection lastErrorMessage]]];

                    continue;
                }
            }



            if(tableType == SPTableTypeTable) {
                // Add the name of table
                [self writeString:[NSString stringWithFormat:@"# %@ %@\n# ------------------------------------------------------------\n\n", NSLocalizedString(@"Dump of table", @"sql export dump of table label"), tableName]];
            }

            // Add a 'DROP TABLE' command if required
            if (sqlOutputIncludeDropSyntax && tableType == SPTableTypeTable) {
                [self writeString:[NSString stringWithFormat:@"DROP %@ IF EXISTS %@;\n\n", ((tableType == SPTableTypeTable) ? @"TABLE" : @"VIEW"), [tableName backtickQuotedString]]];
            }

            // Add the create syntax for the table if specified in the export dialog
            if (sqlOutputIncludeStructure && createTableSyntax && tableType == SPTableTypeTable) {

                if ([createTableSyntax isKindOfClass:[NSData class]]) {
#warning This doesn't make sense. If the NSData really contains a string it would be in utf8, utf8mb4 or a mysql pre-4.1 legacy charset, but not in the export output charset. This whole if() is likely a side effect of the BINARY flag confusion (#2700)
                    createTableSyntax = [[NSString alloc] initWithData:createTableSyntax encoding:[self exportOutputEncoding]];
                }

                // If necessary strip out the AUTO_INCREMENT from the table structure definition
                if (![self sqlOutputIncludeAutoIncrement]) {
                    createTableSyntax = [createTableSyntax stringByReplacingOccurrencesOfRegex:[NSString stringWithFormat:@"AUTO_INCREMENT=[0-9]+ "] withString:@""];
                }

                [self writeUTF8String:createTableSyntax];
                [self writeUTF8String:@";\n\n"];
            }

            // Add the table content if required
            if (sqlOutputIncludeContent && (tableType == SPTableTypeTable)) {
                // Retrieve the table details via the data class, and use it to build an array containing column numeric status
                NSDictionary *tableDetails = [NSDictionary dictionaryWithDictionary:[sqlTableDataInstance informationForTable:tableName fromDatabase:nil]];

                NSUInteger colCount = [[tableDetails objectForKey:@"columns"] count];
                NSUInteger colCountRetained = colCount;

                // Counts the number of GENERATED type fields if columns should be excluded from rows
                if (!sqlOutputIncludeGeneratedColumns) {
                    for (NSUInteger j = 0; j < colCount; j++)
                    {
                        NSDictionary *theColumnDetail = [[tableDetails objectForKey:@"columns"] safeObjectAtIndex:j];
                        NSString *generatedAlways = [theColumnDetail objectForKey:@"generatedalways"];
                        if (generatedAlways) {
                            colCountRetained--;
                        }
                    }
                }

                NSMutableArray *rawColumnNames = [NSMutableArray arrayWithCapacity:(colCountRetained)];
                NSMutableArray *queryColumnDetails = [NSMutableArray arrayWithCapacity:(colCountRetained)];

                BOOL *useRawDataForColumnAtIndex = calloc(colCountRetained, sizeof(BOOL));
                BOOL *useRawHexDataForColumnAtIndex = calloc(colCountRetained, sizeof(BOOL));

                // Determine whether raw data can be used for each column during processing - safe numbers and hex-encoded data.
                NSUInteger jj = 0;
                for (NSUInteger j = 0; j < colCount; j++)
                {
                    NSDictionary *theColumnDetail = [[tableDetails objectForKey:@"columns"] safeObjectAtIndex:j];
                    NSString *theTypeGrouping = [theColumnDetail objectForKey:@"typegrouping"];
                    NSString *generatedAlways = [theColumnDetail objectForKey:@"generatedalways"];

                    if ( sqlOutputIncludeGeneratedColumns || !generatedAlways ) {
                        // Start by setting the column as non-safe
                        useRawDataForColumnAtIndex[jj] = NO;
                        useRawHexDataForColumnAtIndex[jj] = NO;

                        // Determine whether the column should be retrieved as hex data from the server - for binary strings, to
                        // avoid encoding issues when processing
                        if ([self sqlOutputEncodeBLOBasHex]
                            && [theTypeGrouping isEqualToString:@"string"]
                            && ([[theColumnDetail objectForKey:@"binary"] boolValue] || [[theColumnDetail objectForKey:@"collation"] hasSuffix:@"_bin"]))
                        {
                            useRawHexDataForColumnAtIndex[j] = YES;
                        }

                        // Floats, integers can be output directly assuming they're non-binary
                        if (![[theColumnDetail objectForKey:@"binary"] boolValue] && ([@[@"integer",@"float"] containsObject:theTypeGrouping]))
                        {
                            useRawDataForColumnAtIndex[jj] = YES;
                        }

                        // Set up the column query string parts
                        [rawColumnNames addObject:[theColumnDetail objectForKey:@"name"]];

                        if (useRawHexDataForColumnAtIndex[jj]) {
                            [queryColumnDetails addObject:[NSString stringWithFormat:@"HEX(%@)", [[theColumnDetail objectForKey:@"name"] mySQLBacktickQuotedString]]];
                        }
                        else {
                            [queryColumnDetails addObject:[[theColumnDetail objectForKey:@"name"] mySQLBacktickQuotedString]];
                        }
                        jj++;
                    }
                }

                // Retrieve the number of rows in the table for progress bar drawing
                NSArray *rowArray = [[connection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM %@", [tableName backtickQuotedString]]] getRowAsArray];

                if ([connection queryErrored] || ![rowArray count]) {
                    [errors appendFormat:@"%@\n", [connection lastErrorMessage]];
                    [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n\n\n", [connection lastErrorMessage]]];
                    free(useRawDataForColumnAtIndex);
                    free(useRawHexDataForColumnAtIndex);
                    continue;
                }

                NSUInteger rowCount = [[rowArray firstObject] integerValue];

                if (rowCount) {
                    // Set up a result set in streaming mode
                    SPMySQLStreamingResult *streamingResult = [connection streamingQueryString:[NSString stringWithFormat:@"SELECT %@ FROM %@", [queryColumnDetails componentsJoinedByString:@", "], [tableName backtickQuotedString]] useLowMemoryBlockingStreaming:([self exportUsingLowMemoryBlockingStreaming])];

                    // Inform the delegate that we are about to start writing data for the current table
                    [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO];

                    NSUInteger queryLength = 0;

                    // Lock the table for writing and disable keys if supported
                    [metaString setString:@""];
                    [metaString appendFormat:@"LOCK TABLES %@ WRITE;\n/*!40000 ALTER TABLE %@ DISABLE KEYS */;\n\n", [tableName backtickQuotedString], [tableName backtickQuotedString]];

                    [self writeString:metaString];

                    // Construct the start of the insertion command
                    [self writeUTF8String:[NSString stringWithFormat:@"INSERT INTO %@ (%@)\nVALUES", [tableName backtickQuotedString], [rawColumnNames componentsJoinedAndBacktickQuoted]]];

                    // Iterate through the rows to construct a VALUES group for each
                    NSUInteger rowsWrittenForTable = 0;
                    NSUInteger rowsWrittenForCurrentStmt = 0;

                    // Inform the delegate that we are about to start writing the data to disk
                    [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO];

                    NSArray *row;
                    while ((row = [streamingResult getRowAsArray]))
                    {

                        if(self.exportOutputFile.fileHandleError != nil){
                            SPMainQSync(^{
                                [(SPExportController*)self->delegate cancelExportForFile:self->exportOutputFile.exportFilePath];
                            });
                            return;
                        }

                        // Check for cancellation flag
                        if ([self isCancelled]) {
                            [connection cancelCurrentQuery];
                            [streamingResult cancelResultLoad];
                            free(useRawDataForColumnAtIndex);
                            free(useRawHexDataForColumnAtIndex);

                            [self endCleanup:oldSqlMode];
                            return;
                        }

                        // Update the progress
                        NSUInteger progress = (NSUInteger)((rowsWrittenForTable + 1) * ([self exportMaxProgress] / rowCount));

                        if (progress > lastProgressValue) {
                            [self setExportProgressValue:progress];
                            lastProgressValue = progress;

                            // Inform the delegate that the export's progress has been updated
                            [delegate performSelectorOnMainThread:@selector(sqlExportProcessProgressUpdated:) withObject:self waitUntilDone:NO];
                        }

                        // Set up the new row as appropriate.  If a new INSERT statement should be created,
                        // set one up; otherwise, set up a new row
                        if ((([self sqlInsertDivider] == SPSQLInsertEveryNDataBytes) && (queryLength >= ([self sqlInsertAfterNValue] * 1024))) ||
                            (([self sqlInsertDivider] == SPSQLInsertEveryNRows) && (rowsWrittenForCurrentStmt == [self sqlInsertAfterNValue])))
                        {
                            [sqlString setString:@";\n\nINSERT INTO "];
                            [sqlString appendString:[tableName backtickQuotedString]];
                            [sqlString appendString:@" ("];
                            [sqlString appendString:[rawColumnNames componentsJoinedAndBacktickQuoted]];
                            [sqlString appendString:@")\nVALUES\n\t("];

                            queryLength = 0;
                            rowsWrittenForCurrentStmt = 0;
                        }
                        else if (rowsWrittenForTable == 0) {
                            [sqlString setString:@"\n\t("];
                        }
                        else {
                            [sqlString setString:@",\n\t("];
                        }

                        for (NSUInteger t = 0; t < colCountRetained; t++)
                        {
                            id object = [row safeObjectAtIndex:t];
                          	NSDictionary *fieldDetails = [[tableDetails safeObjectForKey:@"columns"] safeObjectAtIndex:t];

                            // Add NULL values directly to the output row; use a pointer comparison to the singleton
                            // instance for speed.
                            if (object == [NSNull null]) {
                                [sqlString appendString:@"NULL"];
                            }

                            // Add trusted raw values directly
                            else if (useRawDataForColumnAtIndex[t]) {
                                [sqlString appendString:object];
                            }

                            // If the field is of type BIT, the values need a binary prefix of b'x'.
                            else if ([[fieldDetails safeObjectForKey:@"type"] isEqualToString:@"BIT"]) {
                                [sqlString appendFormat:@"b'%@'", [object description]];
                            }

                            // Add pre-encoded hex types (binary strings) as enclosed but otherwise trusted data
                            else if (useRawHexDataForColumnAtIndex[t]) {
                                [sqlString appendFormat:@"X'%@'", object];
                            }

                            // GEOMETRY data types directly as hex data
                            else if ([object isKindOfClass:[SPMySQLGeometryData class]]) {
                                [sqlString appendString:[connection escapeAndQuoteData:[object data]]];
                            }

                            // Add zero-length data or strings as an empty string
                            else if ([object length] == 0) {
                                [sqlString appendString:@"''"];
                            }

                            // Add other data types as hex data
                            else if ([object isKindOfClass:[NSData class]]) {

                                if ([self sqlOutputEncodeBLOBasHex]) {
                                    [sqlString appendString:[connection escapeAndQuoteData:object]];
                                }
                                else {
                                    NSString *data = [[NSString alloc] initWithData:object encoding:[self exportOutputEncoding]];

                                    if (data == nil) {
                                    // warning This can corrupt data! Check if this case ever happens and if so, export as hex-string
                                      data = [[NSString alloc] initWithData:object encoding:NSASCIIStringEncoding];
                                    }
                                  
                                    NSString *fieldTypeGroup = [fieldDetails objectForKey:@"typegrouping"];
                                  	if ([fieldTypeGroup isEqualToString:@"textdata"] || [fieldTypeGroup isEqualToString:@"string"]) {
                                      [sqlString appendStringOrNil:[connection escapeAndQuoteString:data]];
                                    } else {
                                      // it's possible that the fieldType could eq to blob
                                      [sqlString appendFormat:@"'%@'", data];
                                    }
                                }
                            }

                            // Otherwise add a quoted string with special characters escaped
                            else {
                                [sqlString appendStringOrNil:[connection escapeAndQuoteString:object]];
                            }

                            // Add the field separator if this isn't the last cell in the row
                            if (t != ([row count] - 1)) [sqlString appendString:@","];
                        }

                        [sqlString appendString:@")"];
                        queryLength += [sqlString length];

                        // Write this row to the file
                        [self writeUTF8String:sqlString];

                        rowsWrittenForTable++;
                        rowsWrittenForCurrentStmt++;
                    }

                    // Complete the command
                    [self writeUTF8String:@";\n\n"];

                    // Unlock the table and re-enable keys if supported
                    [metaString setString:@""];
                    [metaString appendFormat:@"/*!40000 ALTER TABLE %@ ENABLE KEYS */;\nUNLOCK TABLES;\n", [tableName backtickQuotedString]];

                    [self writeUTF8String:metaString];

                    // Release the result set
                }

                free(useRawDataForColumnAtIndex);
                free(useRawHexDataForColumnAtIndex);

                if ([connection queryErrored]) {
                    [errors appendFormat:@"%@\n", [connection lastErrorMessage]];

                    if ([self sqlOutputIncludeErrors]) {
                        [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n", [connection lastErrorMessage]]];
                    }
                }
            }

            // Add triggers if the structure export was enabled
            if (sqlOutputIncludeStructure) {
                SPMySQLResult *queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */", [tableName tickQuotedString]]];

                [queryResult setReturnDataAsStrings:YES];

                if ([queryResult numberOfRows]) {

                    [metaString setString:@"\n"];
                    [metaString appendString:@"DELIMITER ;;\n"];

                    for (NSUInteger s = 0; s < [queryResult numberOfRows]; s++)
                    {

                        if(self.exportOutputFile.fileHandleError != nil){
                            SPMainQSync(^{
                                [(SPExportController*)self->delegate cancelExportForFile:self->exportOutputFile.exportFilePath];
                            });
                            return;
                        }

                        // Check for cancellation flag
                        if ([self isCancelled]) {
                            [self endCleanup:oldSqlMode];
                            return;
                        }

                        NSDictionary *triggers = [[NSDictionary alloc] initWithDictionary:[queryResult getRowAsDictionary]];

                        // Definer is user@host but we need to escape it to `user`@`host`
                        NSArray *triggersDefiner = [[triggers objectForKey:@"Definer"] componentsSeparatedByString:@"@"];

                        [metaString appendFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\" */;;\n/*!50003 CREATE */ ", [triggers objectForKey:@"sql_mode"]];
                        [metaString appendFormat:@"/*!50017 DEFINER=%@@%@ */ /*!50003 TRIGGER %@ %@ %@ ON %@ FOR EACH ROW %@ */;;\n",
                         [[triggersDefiner firstObject] backtickQuotedString],
                         [[triggersDefiner safeObjectAtIndex:1] backtickQuotedString],
                         [[triggers objectForKey:@"Trigger"] backtickQuotedString],
                         [triggers objectForKey:@"Timing"],
                         [triggers objectForKey:@"Event"],
                         [[triggers objectForKey:@"Table"] backtickQuotedString],
                         [triggers objectForKey:@"Statement"]];
                    }

                    [metaString appendString:@"DELIMITER ;\n/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"];

                    [self writeUTF8String:metaString];
                }

                if ([connection queryErrored]) {
                    [errors appendFormat:@"%@\n", [connection lastErrorMessage]];

                    if ([self sqlOutputIncludeErrors]) {
                        [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n", [connection lastErrorMessage]]];
                    }
                }
            }

            // Add an additional separator between tables
            [self writeUTF8String:@"\n\n"];
        }
    }

    // Process any deferred views, adding commands to delete the placeholder tables and add the actual views
    for (NSString *viewName in viewSyntaxes)
    {

        if(self.exportOutputFile.fileHandleError != nil){
            SPMainQSync(^{
                [(SPExportController*)self->delegate cancelExportForFile:self->exportOutputFile.exportFilePath];
            });
            return;
        }

        // Check for cancellation flag
        if ([self isCancelled]) {
            [self endCleanup:oldSqlMode];
            return;
        }

        [metaString setString:@""];

        // Add the name of View
        [self writeString:[NSString stringWithFormat:@"# %@ %@\n# ------------------------------------------------------------\n\n", NSLocalizedString(@"Dump of view", @"sql export dump of view label"), viewName]];

        // Add the View create statement
        [metaString appendFormat:@"%@;\n\n", [viewSyntaxes objectForKey:viewName]];

        [self writeUTF8String:metaString];
    }

    // Export procedures and functions
    for (NSString *procedureType in @[@"PROCEDURE", @"FUNCTION"])
    {

        if(self.exportOutputFile.fileHandleError != nil){
            SPMainQSync(^{
                [(SPExportController*)self->delegate cancelExportForFile:self->exportOutputFile.exportFilePath];
            });
            return;
        }
        // Check for cancellation flag
        if ([self isCancelled]) {
            [self endCleanup:oldSqlMode];
            return;
        }

        // Retrieve the array of selected procedures or functions, and skip export if not selected
        NSMutableArray *items;

        if ([procedureType isEqualToString:@"PROCEDURE"]) items = procs;
        else items = funcs;

        if ([items count] == 0) continue;

        // Retrieve the definitions
        SPMySQLResult *queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */", procedureType,
                                                              [[self sqlDatabaseName] tickQuotedString]]];

        [queryResult setReturnDataAsStrings:YES];

        if ([queryResult numberOfRows]) {

            [metaString setString:@"\n"];
            [metaString appendFormat:@"--\n-- Dumping routines (%@) for database %@\n--\nDELIMITER ;;\n\n", procedureType,
             [[self sqlDatabaseName] tickQuotedString]];

            // Loop through the definitions, exporting if enabled
            for (NSUInteger s = 0; s < [queryResult numberOfRows]; s++) {
                @autoreleasepool {
                    if(self.exportOutputFile.fileHandleError != nil){
                        SPMainQSync(^{
                            [(SPExportController*)self->delegate cancelExportForFile:self->exportOutputFile.exportFilePath];
                        });
                        return;
                    }

                    // Check for cancellation flag
                    if ([self isCancelled]) {
                        [self endCleanup:oldSqlMode];
                        return;
                    }

                    NSDictionary *proceduresList = [[NSDictionary alloc] initWithDictionary:[queryResult getRowAsDictionary]];
                    NSString *procedureName = [NSString stringWithFormat:@"%@", [proceduresList objectForKey:@"Name"]];

                    // Only proceed if the item is in the list of items
                    BOOL itemFound = NO;
                    BOOL sqlOutputIncludeStructure = NO;
                    BOOL sqlOutputIncludeDropSyntax = NO;
                    for (NSArray *item in items)
                    {

                        if(self.exportOutputFile.fileHandleError != nil){
                            SPMainQSync(^{
                                [(SPExportController*)self->delegate cancelExportForFile:self->exportOutputFile.exportFilePath];
                            });
                            return;
                        }

                        // Check for cancellation flag
                        if ([self isCancelled]) {
                            [self endCleanup:oldSqlMode];
                            return;
                        }

                        if ([[item firstObject] isEqualToString:procedureName]) {
                            itemFound = YES;
                            sqlOutputIncludeStructure  = [[item safeObjectAtIndex:1] boolValue];
                            sqlOutputIncludeDropSyntax = [[item safeObjectAtIndex:3] boolValue];
                            break;
                        }
                    }
                    if (!itemFound) {
                        continue;
                    }

                    if (sqlOutputIncludeStructure || sqlOutputIncludeDropSyntax)
                        [metaString appendFormat:@"# Dump of %@ %@\n# ------------------------------------------------------------\n\n", procedureType, procedureName];

                    // Add the 'DROP' command if required
                    if (sqlOutputIncludeDropSyntax) {
                        [metaString appendFormat:@"/*!50003 DROP %@ IF EXISTS %@ */;;\n", procedureType,
                         [procedureName backtickQuotedString]];
                    }

                    // Only continue if the 'CREATE SYNTAX' is required
                    if (!sqlOutputIncludeStructure) {
                        continue;
                    }

                    // Definer is user@host but we need to escape it to `user`@`host`
                    NSArray *procedureDefiner = [[proceduresList objectForKey:@"Definer"] componentsSeparatedByString:@"@"];

                    NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@",
                                                [[procedureDefiner firstObject] backtickQuotedString],
                                                [[procedureDefiner safeObjectAtIndex:1] backtickQuotedString]];

                    SPMySQLResult *createProcedureResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE %@ %@ */", procedureType,
                                                                                    [procedureName backtickQuotedString]]];
                    [createProcedureResult setReturnDataAsStrings:YES];
                    if ([connection queryErrored]) {
                        [errors appendFormat:@"%@\n", [connection lastErrorMessage]];

                        if ([self sqlOutputIncludeErrors]) {
                            [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n", [connection lastErrorMessage]]];
                        }
                        continue;
                    }

                    NSDictionary *procedureInfo = [[NSDictionary alloc] initWithDictionary:[createProcedureResult getRowAsDictionary]];

                    [metaString appendFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\"*/;;\n", [procedureInfo objectForKey:@"sql_mode"]];

                    NSString *createProcedure = [procedureInfo objectForKey:[NSString stringWithFormat:@"Create %@", [procedureType capitalizedString]]];

                    // A NULL result indicates a permission problem
                    if ([createProcedure isNSNull]) {
                        NSString *errorString = [NSString stringWithFormat:NSLocalizedString(@"Could not export the %@ '%@' because of a permissions error.\n", @"Procedure/function export permission error"), procedureType, procedureName];
                        [errors appendString:errorString];
                        if ([self sqlOutputIncludeErrors]) {
                            [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n", errorString]];
                        }
                        continue;
                    }

                    NSRange procedureRange    = [createProcedure rangeOfString:procedureType options:NSCaseInsensitiveSearch];
                    NSString *procedureBody   = [createProcedure substringFromIndex:procedureRange.location];

                    // /*!50003 CREATE*/ /*!50020 DEFINER=`sequelpro`@`%`*/ /*!50003 PROCEDURE `p`()
                    // 													  BEGIN
                    // 													  /* This procedure does nothing */
                    // END */;;
                    //
                    // Build the CREATE PROCEDURE string to include MySQL Version limiters
                    [metaString appendFormat:@"/*!50003 CREATE*/ /*!50020 DEFINER=%@*/ /*!50003 %@ */;;\n\n/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;\n", escapedDefiner, procedureBody];

                }
            }

            [metaString appendString:@"DELIMITER ;\n"];

            [self writeUTF8String:metaString];
        }

        if ([connection queryErrored]) {
            [errors appendFormat:@"%@\n", [connection lastErrorMessage]];

            if ([self sqlOutputIncludeErrors]) {
                [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n", [connection lastErrorMessage]]];
            }
        }
    }

    // Restore unique checks, foreign key checks, and other settings saved at the start
    [metaString setString:@"\n"];
    [metaString appendString:@"/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n"];
    [metaString appendString:@"/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n"];
    [metaString appendString:@"/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n"];

    // Restore the client encoding to the original encoding before import
    [metaString appendString:@"/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n"];
    [metaString appendString:@"/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n"];
    [metaString appendString:@"/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n"];

    // Write footer-type information to the file
    [self writeUTF8String:metaString];

    // Set export errors
    [self setSqlExportErrors:errors];

    // Close the file
    [[self exportOutputFile] close];

    // Mark the process as not running
    [self setExportProcessIsRunning:NO];

    // Inform the delegate that the export process is complete
    [delegate performSelectorOnMainThread:@selector(sqlExportProcessComplete:) withObject:self waitUntilDone:NO];

    [self endCleanup:oldSqlMode];
}

- (void)endCleanup:(NSString *)oldSqlMode {
    if(oldSqlMode) {
        [connection queryString:[NSString stringWithFormat:@"SET SQL_MODE=%@",[oldSqlMode tickQuotedString]]];
    }
}

/**
 * Returns whether or not any export errors occurred by examing the length of the errors string.
 *
 * @return A BOOL indicating the occurrence of errors
 */
- (BOOL)didExportErrorsOccur
{
    return ([[self sqlExportErrors] length] != 0);
}

/**
 * Retrieve information for a view and use that to construct a CREATE TABLE string for an equivalent basic
 * table. Allows the construction of placeholder tables to resolve view interdependencies within dumps.
 *
 * @param viewName The name of the view for which the placeholder is to be created for.
 *
 * @return The CREATE TABLE placeholder syntax
 */
- (NSString *)_createViewPlaceholderSyntaxForView:(NSString *)viewName
{
    NSUInteger i, j;
    NSMutableString *placeholderSyntax;

    // Get structured information for the view via the SPTableData parsers
    NSDictionary *viewInformation = [sqlTableDataInstance informationForView:viewName];

    if (!viewInformation) return nil;

    NSArray *viewColumns = [viewInformation objectForKey:@"columns"];

    // Set up the start of the placeholder string and initialise an empty field string
    placeholderSyntax = [[NSMutableString alloc] initWithFormat:@"CREATE TABLE %@ (\n", [viewName backtickQuotedString]];


    // Loop through the columns, creating an appropriate column definition for each and appending it to the syntax string
    for (i = 0; i < [viewColumns count]; i++) {
        @autoreleasepool {
            NSDictionary *column = [viewColumns safeObjectAtIndex:i];

            NSMutableString *fieldString = [[NSMutableString alloc] initWithString:[[column objectForKey:@"name"] backtickQuotedString]];

            // Add the type and length information as appropriate
            if ([column objectForKey:@"length"]) {
                NSString *length = [column objectForKey:@"length"];
                NSString *decimals = [column objectForKey:@"decimals"];
                if([decimals length]) {
                    length = [length stringByAppendingFormat:@",%@", decimals];
                }
                [fieldString appendFormat:@" %@(%@)", [column objectForKey:@"type"], length];
            }
            else if ([column objectForKey:@"values"]) {
                [fieldString appendFormat:@" %@(", [column objectForKey:@"type"]];

                for (j = 0; j < [[column objectForKey:@"values"] count]; j++)
                {
                    [fieldString appendString:[connection escapeAndQuoteString:[[column safeObjectForKey:@"values"] safeObjectAtIndex:j]]];
                    if ((j + 1) != [[column objectForKey:@"values"] count]) {
                        [fieldString appendString:@","];
                    }
                }

                [fieldString appendString:@")"];
            }
            else {
                [fieldString appendFormat:@" %@", [column objectForKey:@"type"]];
            }

            // Field specification details
            if ([[column objectForKey:@"unsigned"] integerValue] == 1) [fieldString appendString:@" UNSIGNED"];
            if ([[column objectForKey:@"zerofill"] integerValue] == 1) [fieldString appendString:@" ZEROFILL"];
            if ([[column objectForKey:@"binary"] integerValue] == 1) [fieldString appendString:@" BINARY"];
            if ([[column objectForKey:@"null"] integerValue] == 0) {
                [fieldString appendString:@" NOT NULL"];
            } else {
                [fieldString appendString:@" NULL"];
            }

            // Provide the field default if appropriate
            if ([column objectForKey:@"default"]) {

                // Some MySQL server versions show a default of NULL for NOT NULL columns - don't export those.
                // Check against the NSNull singleton instance for speed.
                if ([column objectForKey:@"default"] == [NSNull null]) {
                    if ([[column objectForKey:@"null"] integerValue]) {
                        [fieldString appendString:@" DEFAULT NULL"];
                    }
                }
                else if (([[column objectForKey:@"type"] isInArray:@[@"TIMESTAMP",@"DATETIME"]]) && [[column objectForKey:@"default"] isMatchedByRegex:SPCurrentTimestampPattern]) {
                    [fieldString appendFormat:@" DEFAULT %@",[column objectForKey:@"default"]];
                }
                else {
                    [fieldString appendFormat:@" DEFAULT %@", [connection escapeAndQuoteString:[column objectForKey:@"default"]]];
                }
            }

            // Extras aren't required for the temp table
            // Add the field string to the syntax string
            [placeholderSyntax appendFormat:@"   %@%@\n", fieldString, (i == [viewColumns count] - 1) ? @"" : @","];
        }
    }

    // Append the remainder of the table string
    [placeholderSyntax appendString:@") ENGINE=MyISAM"];

    // Clean up and return

    return placeholderSyntax;
}

@end
